package com.lry.util.mysql.util;

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLValuableExpr;
import com.alibaba.druid.sql.ast.statement.SQLColumnConstraint;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLColumnPrimaryKey;
import com.alibaba.druid.sql.ast.statement.SQLNullConstraint;
import com.alibaba.druid.sql.ast.statement.SQLPrimaryKey;
import com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.lry.util.mysql.ColumnInfo;
import com.lry.util.mysql.TableInfo;
import com.lry.util.mysql.TableName;
import com.mysql.cj.MysqlType;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.select.Limit;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import org.apache.commons.lang3.StringUtils;
import org.springframework.lang.Nullable;
import org.springframework.util.CollectionUtils;

/**
 * sql工具
 *
 * @author ALI
 * @since 2023/1/12
 */
public class SqlUtil {

    // region sql语句转换

    /**
     * 构建查询sql，
     *
     * @param tableName 表名，不可为空
     * @param sql       基础sql，可为空
     * @param fieldMap  额外添加的sql参数，可为空，需要拼接的参数
     */
    public static String getSelectWhereAndSql(String tableName, String sql, Map<String, String> fieldMap)
    throws JSQLParserException {
        String eSql = SqlUtil.getDefaultSelectSql(tableName);
        if (StringUtils.isNotBlank(sql)) {
            eSql = sql;
        }
        // 当有其他条件时需要加上
        Statements statements = CCJSqlParserUtil.parseStatements(eSql);
        for (net.sf.jsqlparser.statement.Statement statement : statements.getStatements()) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = buildAndConditionWhere((Select) statement, fieldMap);
            //            addLimit(request, plainSelect);
        }
        return statements.toString();
    }

    /**
     * 根据sql获取 查询的表
     */
    public static String getSelectTable(String sql) throws JSQLParserException {
        Statements statements = CCJSqlParserUtil.parseStatements(sql);
        List<net.sf.jsqlparser.statement.Statement> statements1 = statements.getStatements();
        for (net.sf.jsqlparser.statement.Statement statement : statements1) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            return plainSelect.getFromItem().toString();
        }
        return null;
    }

    /**
     * 根据查询sql和指定的列名，构建is not null sql
     *
     * @param sql     select语句
     * @param columns is not null的列名
     */
    public static String getIsNotNullSql(String sql, Collection<String> columns) throws JSQLParserException {
        if (columns == null) {
            throw new RuntimeException("is not sql 应指明字段名");
        }
        Statements statements = CCJSqlParserUtil.parseStatements(sql);
        for (net.sf.jsqlparser.statement.Statement statement : statements.getStatements()) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            Expression where = plainSelect.getWhere();
            if (where == null) {
                // 取第一个拼接，其他的拼and
                List<String> otherColumn = new ArrayList<>();
                int i = 0;
                for (String column : columns) {
                    if (i == 0) {
                        if (StringUtils.isNotBlank(column)) {
                            where = new Parenthesis(isNotNull(new Column(column)));
                            plainSelect.setWhere(where);
                        }
                    } else {
                        otherColumn.add(column);
                    }
                    ++i;
                }
                for (String columnField : otherColumn) {
                    if (StringUtils.isBlank(columnField)) {
                        continue;
                    }
                    Column column = new Column(columnField);
                    plainSelect.setWhere(new AndExpression(where, isNotNull(column)));
                }
            } else {
                if (columns.size() > 0) {
                    for (String columnField : columns) {
                        if (StringUtils.isBlank(columnField)) {
                            continue;
                        }
                        Column column = new Column(columnField);
                        plainSelect.setWhere(new AndExpression(where, isNotNull(column)));
                    }
                }
            }

        }
        return statements.toString();
    }

    /**
     * 通过select sql转化为count sql
     *
     * @param selectSql select sql，可以有条件
     */
    public static String getCountSql(String selectSql) throws JSQLParserException {
        Statements statements = CCJSqlParserUtil.parseStatements(selectSql);
        for (net.sf.jsqlparser.statement.Statement statement : statements.getStatements()) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            plainSelect.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("COUNT(*)"))));
            plainSelect.setLimit(null);
        }
        return statements.toString();
    }

    /**
     * 将查询的列替换为指定的列
     *
     * @param selectSql select sql
     * @param columns   要查询的列
     */
    public static String replaceSelectColumn(String selectSql, List<String> columns) throws JSQLParserException {
        Statements statements = CCJSqlParserUtil.parseStatements(selectSql);
        for (net.sf.jsqlparser.statement.Statement statement : statements.getStatements()) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            List<SelectItem> realColumns = new ArrayList<>();
            for (String column : columns) {
                realColumns.add(new SelectExpressionItem(new Column(column)));
            }
            plainSelect.setSelectItems(realColumns);
            plainSelect.setLimit(null);
        }
        return statements.toString();
    }

    /**
     * sql语句评价 limit
     *
     * @param sql      sql语句
     * @param pageSize 页大小
     */
    public static String addLimit(String sql, int pageSize) throws JSQLParserException {
        Statements statements = CCJSqlParserUtil.parseStatements(sql);
        for (net.sf.jsqlparser.statement.Statement statement : statements.getStatements()) {
            if (!(statement instanceof Select)) {
                continue;
            }
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            addLimit(plainSelect, pageSize);
        }
        return statements.toString();
    }

    /**
     * 获取默认的select sql
     *
     * @param tableName 表名
     */
    public static String getDefaultSelectSql(String tableName) {
        return String.format("SELECT * FROM `%s`", tableName);
    }

    /**
     * 构建where参数条件
     *
     * @param statement select 声明
     * @param fieldMap  参数对象
     */
    private static PlainSelect buildAndConditionWhere(Select statement, Map<String, String> fieldMap) {
        PlainSelect plainSelect = (PlainSelect) statement.getSelectBody();
        Expression where = plainSelect.getWhere();
        if (where == null) {
            if (CollectionUtils.isEmpty(fieldMap)) {
                return plainSelect;
            }
            // 取第一个拼接，其他的拼and
            String key = null;
            String value = null;
            List<Map.Entry<String, String>> otherField = new ArrayList<>();
            int i = 0;
            for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                if (i++ == 0) {
                    key = entry.getKey();
                    if (StringUtils.isBlank(key)) {
                        continue;
                    }
                    value = entry.getValue();
                } else {
                    otherField.add(entry);
                }
            }
            if (key == null) {
                return plainSelect;
            }
            Column firstColumn = new Column(key);
            where = new Parenthesis(whereEq(firstColumn, value));
            plainSelect.setWhere(where);
            if (!otherField.isEmpty()) {
                for (Map.Entry<String, String> entry : otherField) {
                    if (StringUtils.isBlank(entry.getKey())) {
                        return plainSelect;
                    }
                    Column column = new Column(entry.getKey());
                    Expression equalsTo = whereEq(column, entry.getValue());
                    plainSelect.setWhere(new AndExpression(where, equalsTo));
                }
            }
        } else {
            if (CollectionUtils.isEmpty(fieldMap)) {
                return plainSelect;
            }
            for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                if (StringUtils.isBlank(entry.getKey())) {
                    return plainSelect;
                }
                Column column = new Column(entry.getKey());
                Expression equalsTo = whereEq(column, entry.getValue());
                plainSelect.setWhere(new AndExpression(where, equalsTo));
            }
        }
        return plainSelect;
    }

    /**
     * = 条件
     */
    private static Expression whereEq(Column field, String value) {
        if (value == null) {
            IsNullExpression isNullExpression = new IsNullExpression();
            isNullExpression.setLeftExpression(field);
            return isNullExpression;
        } else {
            EqualsTo equalsTo = new EqualsTo();
            equalsTo.setLeftExpression(field);
            equalsTo.setRightExpression(new StringValue(value));
            return equalsTo;
        }
    }

    private static Expression isNotNull(Column field) {
        IsNullExpression isNullExpression = new IsNullExpression();
        isNullExpression.setLeftExpression(field);
        isNullExpression.setNot(true);
        return isNullExpression;
    }

    private static void addLimit(PlainSelect plainSelect, int pageSize) {
        Limit limit = new Limit();
        //            limit.setOffset(new LongValue(0));
        limit.setRowCount(new LongValue(pageSize));
        plainSelect.setLimit(limit);
    }

    // endregion

    // region sql语句解析处理

    /**
     * 转换为代码可执行的sql
     */
    public static String toExcutorSql(String sqlString) {
        sqlString = sqlString.replaceAll("(CHARACTER SET utf8 COLLATE utf8_general_ci)"
                                             + "|(ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci)"
                                             + "|(ROW_FORMAT = Compact)"
                                             + "|(USING BTREE)"
                                             + "|(SET NAMES utf8mb4;)"
                                             + "|(SET FOREIGN_KEY_CHECKS = 0;)"
                                             + "|(SET FOREIGN_KEY_CHECKS = 1;)", "");
        return sqlString;
    }

    /**
     * 解析cresate sql
     *
     * @param createSql 建表sql
     * @return 结果
     */
    public static List<TableInfo> parseCreateSql(String createSql) {
        SQLStatementParser sqlStatementParser = SQLParserUtils
            .createSQLStatementParser(toExcutorSql(createSql), "mysql");
        List<SQLStatement> sqlStatements = sqlStatementParser.parseStatementList();
        List<TableInfo> resultTable = new ArrayList<>();
        for (SQLStatement ss : sqlStatements) {
            if (!(ss instanceof MySqlCreateTableStatement)) {
                // 只处理创建sql
                continue;
            }
            MySqlCreateTableStatement sqlStatement = (MySqlCreateTableStatement) ss;
            TableInfo result = new TableInfo();
            result.setName(sqlStatement.getTableSource().getName().getSimpleName().replaceAll("`", ""));
            if (sqlStatement.getComment() != null) {
                result.setComment(((SQLCharExpr) sqlStatement.getComment()).getText());
            }
            List<SQLTableElement> tableElementList = sqlStatement.getTableElementList();

            ColumnInfo primaryKey = null;
            Map<String, ColumnInfo> columnInfoMap = new HashMap<>();
            for (SQLTableElement ele : tableElementList) {
                if (ele instanceof SQLColumnDefinition) {
                    ColumnInfo columnInfo = parseColumnDefinition((SQLColumnDefinition) ele);
                    columnInfoMap.put(columnInfo.getName(), columnInfo);
                } else if (ele instanceof SQLPrimaryKey) {
                    primaryKey = parsePrimaryKey((MySqlPrimaryKey) ele);
                } else if (ele instanceof MySqlKey) {
                    ColumnInfo indexColumn = parseTableIndex((MySqlKey) ele);
                }
            }
            if (primaryKey != null) {
                ColumnInfo key = columnInfoMap.get(primaryKey.getName());
                key.setNullable(false);
                key.setPrimaryKey(true);
                key.setUnique(true);
            }
            result.setFields(new ArrayList<>(columnInfoMap.values()));
            resultTable.add(result);
        }
        return resultTable;
    }

    public static List<TableInfo> parseCreateSql(String createSql, String mark) {
        SQLStatementParser sqlStatementParser = SQLParserUtils
            .createSQLStatementParser(toExcutorSql(createSql), "mysql");
        List<SQLStatement> sqlStatements = sqlStatementParser.parseStatementList();
        List<TableInfo> resultTable = new ArrayList<>();
        for (SQLStatement ss : sqlStatements) {
            if (!(ss instanceof MySqlCreateTableStatement)) {
                // 只处理创建sql
                continue;
            }
            MySqlCreateTableStatement sqlStatement = (MySqlCreateTableStatement) ss;
            TableInfo result = new TableInfo();

            result.setName(sqlStatement.getTableSource().getName().getSimpleName().replaceAll("`", ""));
            result.setNewName(result.getName() + "_" + mark);

            if (sqlStatement.getComment() != null) {
                result.setComment(((SQLCharExpr) sqlStatement.getComment()).getText());
            }
            List<SQLTableElement> tableElementList = sqlStatement.getTableElementList();

            ColumnInfo primaryKey = null;
            Map<String, ColumnInfo> columnInfoMap = new HashMap<>();
            for (SQLTableElement ele : tableElementList) {
                if (ele instanceof SQLColumnDefinition) {
                    ColumnInfo columnInfo = parseColumnDefinition((SQLColumnDefinition) ele);
                    columnInfoMap.put(columnInfo.getName(), columnInfo);
                } else if (ele instanceof SQLPrimaryKey) {
                    primaryKey = parsePrimaryKey((MySqlPrimaryKey) ele);
                } else if (ele instanceof MySqlKey) {
                    ColumnInfo indexColumn = parseTableIndex((MySqlKey) ele);
                }
            }
            if (primaryKey != null) {
                ColumnInfo key = columnInfoMap.get(primaryKey.getName());
                key.setNullable(false);
                key.setPrimaryKey(true);
                key.setUnique(true);
            }
            result.setFields(new ArrayList<>(columnInfoMap.values()));
            resultTable.add(result);
        }
        return resultTable;
    }

    // endregion

    // region 数据库表解析操作

    public static ColumnInfo parseColumnDefinition(SQLColumnDefinition t) {
        ColumnInfo temp = new ColumnInfo();
        if (t.getComment() != null) {
            temp.setComment(((SQLCharExpr) t.getComment()).getText());
        }
        temp.setName(t.getNameAsString());
        // 清楚特殊字符
        temp.setName(temp.getName().replaceAll("`", ""));
        temp.setType(t.getDataType().getName());
        if (t.getDefaultExpr() != null) {
            temp.setDefaultValue(((SQLValuableExpr) t.getDefaultExpr()).getValue());
        }
        if (t.getDataType().getArguments() != null && !t.getDataType().getArguments().isEmpty()) {
            Object value = null;
            List<SQLExpr> lengthArg = t.getDataType().getArguments();
            if (lengthArg.size() == 1) {
                value = ((SQLValuableExpr) lengthArg.get(0)).getValue();
            } else {
                value = lengthArg.stream().map(SQLExpr::toString).collect(Collectors.joining(","));
            }
            Optional.ofNullable(value).ifPresent(d -> temp.setLength(String.valueOf(d)));
        }
        List<SQLColumnConstraint> constraints = t.getConstraints();
        for (SQLColumnConstraint constraint : constraints) {
            if (constraint instanceof SQLNullConstraint) {
                temp.setNullable(true);
            } else if (constraint instanceof SQLColumnPrimaryKey) {
                temp.setUnique(true);
                temp.setPrimaryKey(true);
            }
        }
        return temp;
    }

    /**
     * 构建默认的表字段对象，默认varchar(1024)，因为不知道导入的数据的长度，所有给1024，说不定还会时2048
     *
     * @param columnName 字段名
     */
    public static ColumnInfo buildDefaultVarcharColumn(String columnName) {
        ColumnInfo column = new ColumnInfo();
        column.setName(columnName);
        column.setLength("1024");
        column.setNullable(true);
        column.setUnique(false);
        column.setDefaultValue(null);
        column.setType(MysqlType.VARCHAR.getName());
        column.setPrimaryKey(false);
        column.setComment(null);
        return column;
    }

    public static ColumnInfo parsePrimaryKey(MySqlPrimaryKey t) {
        ColumnInfo temp = new ColumnInfo();
        if (t.getComment() != null) {
            temp.setComment(((SQLCharExpr) t.getComment()).getText());
        }
        if (t.getColumns() == null) {
            return null;
        }
        for (SQLSelectOrderByItem column : t.getColumns()) {
            temp.setName(((SQLIdentifierExpr) column.getExpr()).getName());
            // 清楚特殊字符
            temp.setName(temp.getName().replaceAll("`", ""));
            temp.setPrimaryKey(true);
            temp.setNullable(false);
            temp.setUnique(true);
        }
        return temp;
    }

    public static ColumnInfo parseTableIndex(MySqlKey t) {
        ColumnInfo temp = new ColumnInfo();
        if (t.getComment() != null) {
            temp.setComment(((SQLCharExpr) t.getComment()).getText());
        }
        temp.setName(t.getName().getSimpleName());
        // 清楚特殊字符
        temp.setName(temp.getName().replaceAll("`", ""));
        if (t.getComment() != null) {
            temp.setComment(((SQLCharExpr) t.getComment()).getText());
        }
        return temp;
    }

    /**
     * 从表中获取表结构
     *
     * @param tables 表名
     * @param conn   链接对象
     * @return List<Map>对象；使用map解耦实体类
     */
    public List<TableInfo> getTableDdl(List<TableName> tables, Connection conn) throws Exception {
        List<TableInfo> tableResult = new ArrayList<>();
        Statement stmt = conn.createStatement();
        for (TableName entry : tables) {
            TableInfo tableInfo = getSingleTableInfo(stmt, entry);
            tableResult.add(tableInfo);
        }
        stmt.close();
        return tableResult;
    }

    /**
     * 从数据库读取表名列表
     *
     * @param databaseName 数据库名称
     * @param conn         数据库链接
     */
    public List<TableName> getTablesFromDb(@Nullable String databaseName, Connection conn)
    throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet tables = metaData.getTables(databaseName, null, null, null);
        List<TableName> result = new ArrayList<>();
        while (tables.next()) {
            result.add(new TableName(tables.getString("TABLE_NAME"), tables.getString("REMARKS")));
        }
        return result;
    }

    /**
     * 获取当个表的ddl信息
     *
     * @param stmt  声明对象
     * @param entry 表名
     */
    public TableInfo getSingleTableInfo(Statement stmt, TableName entry) throws SQLException {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setName(entry.getName());
        tableInfo.setComment(entry.getComment());
        ResultSet rs = stmt.executeQuery("show full columns from " + entry.getName());
        List<ColumnInfo> fields = new ArrayList<>();
        while (rs.next()) {
            ColumnInfo field = new ColumnInfo();
            field.setName(rs.getString("Field"));
            field.setType(rs.getString("Type"));
            field.setNullable("YES".equals(rs.getString("Null")));
            field.setPrimaryKey("PRI".equals(rs.getString("Key")));
            field.setDefaultValue(rs.getString("Default"));
            field.setComment(rs.getString("Comment"));
            fields.add(field);
        }
        tableInfo.setFields(fields);
        return tableInfo;
    }

    // endregion
}
